package ph.com.gs3.formalistics.model.dao;

import java.util.ArrayList;
import java.util.List;

import org.json.JSONException;
import org.json.JSONObject;

import ph.com.gs3.formalistics.global.utilities.logging.FLLogger;
import ph.com.gs3.formalistics.model.tables.DocumentsTable;
import ph.com.gs3.formalistics.model.valueobjects.business.document.Document;
import ph.com.gs3.formalistics.model.valueobjects.business.document.DocumentStub;
import ph.com.gs3.formalistics.model.valueobjects.business.document.Document.DocumentStar;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class DocumentsDAO extends SQLiteDataAccessObject {

	private String[] tableColumns;
	private String[] stubTableColumns;

	public DocumentsDAO(Context context) {
		super(context);

		tableColumns = new DocumentsTable().getTableColumns();
		stubTableColumns = new String[] { DocumentsTable.COL_WEB_ID,
		        DocumentsTable.COL_FORM_WEB_ID, DocumentsTable.COL_DATE_UPDATED };

	}

	// ==============================================================
	// {{ Insert & Update Methods

	public Document saveOrUpdateDocument(Document document) throws JSONException {

		ContentValues cv = createCVFromDocument(document);

		String server = document.getServer();
		String formId = document.getFormWebId();
		String documentWebId = document.getWebId();

		Document resultingDocument = null;

		if (isDocumentExisting(server, formId, documentWebId)) {
			// throws JSONException
			resultingDocument = updateDocument(document);
		} else {
			int documentDbId = (int) database.insertOrThrow(DocumentsTable.NAME, null, cv);
			// throws JSONException
			resultingDocument = getDocument(Integer.toString(documentDbId));
		}

		return resultingDocument;

	}

	public Document updateDocument(Document document) throws JSONException {

		ContentValues cv = createCVFromDocument(document);

		String server = document.getServer();
		String documentWebId = document.getWebId();
		String documentFormWebId = document.getFormWebId();

		// @formatter:off
		String whereClause = 	DocumentsTable.COL_WEB_ID 		+ "=? AND " + 
								DocumentsTable.COL_FORM_WEB_ID 	+ "=? AND " +
								DocumentsTable.COL_SERVER 		+ "=?";
		String[] whereArgs = new String[] { documentWebId, documentFormWebId, server };
		// @formatter:on

		long affectedRows = database.update(DocumentsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows > 0) {
			// throws JSONException
			return getDocument(documentWebId, documentFormWebId, server);
		} else {
			throw new SQLException("Failed to update form.");
		}

	}

	public void markDocumentStar(String documentFormWebId, String documentWebId, String server,
	        DocumentStar star) {

		String isStarredRawValue = star == DocumentStar.STARRED ? "1" : "0";

		ContentValues cv = new ContentValues();
		cv.put(DocumentsTable.COL_IS_STARRED, isStarredRawValue);

		// @formatter:off
		String whereClause 	= DocumentsTable.COL_WEB_ID 		+ "=? AND " 
							+ DocumentsTable.COL_FORM_WEB_ID 	+ "=? AND " 
							+ DocumentsTable.COL_SERVER 		+ "=?";
		String[] whereArgs = new String[] { documentWebId, documentFormWebId, server };
		// @formatter:on

		long affectedRows = database.update(DocumentsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows <= 0) {
			throw new SQLException("Failed to update document.");
		}

	}

	public void markDocumentStar(String documentDbId, DocumentStar star) {

		String isStarredRawValue = star == DocumentStar.STARRED ? "1" : "0";

		ContentValues cv = new ContentValues();
		cv.put(DocumentsTable.COL_IS_STARRED, isStarredRawValue);

		String whereClause = DocumentsTable.COL_ID + "=?";
		String[] whereArgs = new String[] { documentDbId };

		long affectedRows = database.update(DocumentsTable.NAME, cv, whereClause, whereArgs);

		if (affectedRows <= 0) {
			throw new SQLException("Failed to update form.");
		}

	}

	// }}

	// ==============================================================
	// {{ Query Methods

	public List<DocumentStub> getFormDocumentStubs(String server, String formWebId) {

		String whereClause = DocumentsTable.COL_SERVER + "=? AND " + DocumentsTable.COL_FORM_WEB_ID
		        + "=?";
		String[] whereArgs = { server, formWebId };

		String orderBy = DocumentsTable.COL_DATE_UPDATED + " DESC";
		
		Cursor cursor = database.query(DocumentsTable.NAME, stubTableColumns, whereClause,
		        whereArgs, null, null, orderBy);

		List<DocumentStub> documents = new ArrayList<>();

		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			documents.add(cursorToDocumentStub(cursor));
			cursor.moveToNext();
		}

		return documents;

	}

	public List<Document> getAllDocuments(String server, String currentUserId,
	        List<String> formWebIdList) {

		String notInClause = "";

		if (formWebIdList != null && formWebIdList.size() > 0) {
			// create not in statement
			notInClause += " AND " + DocumentsTable.COL_FORM_WEB_ID + " IN (";
			for (String formWebId : formWebIdList) {
				notInClause += "'" + formWebId + "',";
			}

			// trim the last ,
			notInClause = notInClause.substring(0, notInClause.length() - 1);
			notInClause += ")";
		} else {
			FLLogger.w(TAG,
			        "Getting all documents from a server without a form/list of forms filter");
		}

		String whereClause = DocumentsTable.COL_SERVER + " = ? AND "
		        + DocumentsTable.COL_DOCUMENT_OWNER_ID + " = ? AND "
		        + DocumentsTable.COL_FORM_WEB_ID + " != ? " + notInClause;
		String[] whereArgs = new String[] { server, currentUserId, "0" };

		String orderBy = DocumentsTable.COL_DATE_UPDATED + " DESC";

		Cursor cursor = database.query(DocumentsTable.NAME, tableColumns, whereClause, whereArgs,
		        null, null, orderBy);

		List<Document> documents = new ArrayList<Document>();

		cursor.moveToFirst();

		while (!cursor.isAfterLast()) {
			try {
				documents.add(cursorToDocument(cursor));
			} catch (JSONException e) {
				FLLogger.e(TAG, "Failed to load document, " + e.getMessage());
			}

			cursor.moveToNext();
		}

		cursor.close();

		return documents;

	}

	public Document getDocument(String dbId) throws JSONException {

		String whereClause = DocumentsTable.COL_ID + " = ?";
		String[] whereArgs = new String[] { dbId };

		Cursor cursor = database.query(DocumentsTable.NAME, tableColumns, whereClause, whereArgs,
		        null, null, null);

		Document document = null;

		if (cursor.moveToFirst() && cursor.getCount() > 0) {
			// throws JSONException
			document = cursorToDocument(cursor);
		}

		cursor.close();

		return document;

	}

	public Document getDocument(String webId, String formWebId, String server) throws JSONException {

		// @formatter:off
		String whereClause = 	DocumentsTable.COL_WEB_ID 		+ "=? AND " + 
								DocumentsTable.COL_FORM_WEB_ID 	+ "=? AND " +
								DocumentsTable.COL_SERVER 		+ "=?";
		String[] whereArgs = new String[] { webId, formWebId, server };
		// @formatter:on

		Cursor cursor = database.query(DocumentsTable.NAME, tableColumns, whereClause, whereArgs,
		        null, null, null);

		Document document = null;

		if (cursor.moveToFirst() && cursor.getCount() > 0) {
			// throws JSONException
			document = cursorToDocument(cursor);
		}

		cursor.close();

		return document;

	}

	public boolean isDocumentExisting(String server, String formWebId, String documentWebId) {

		// FLLogger.d(TAG, "server: " + server + " document web id: " + documentWebId);

		// @formatter:off
		String whereClause 	= DocumentsTable.COL_SERVER 		+ " = ? AND "
		        			+ DocumentsTable.COL_FORM_WEB_ID 	+ " = ? AND " 
		        			+ DocumentsTable.COL_WEB_ID 		+ " = ?";

		String[] whereArgs = new String[] { server, formWebId, documentWebId };

		Cursor cursor = database.query(DocumentsTable.NAME, new String[] { DocumentsTable.COL_ID },
		        whereClause, whereArgs, null, null, null);
		// @formatter:on

		int id = -1;
		if (cursor.moveToFirst() && cursor.getCount() > 0) {
			id = cursor.getInt(0);
		}

		cursor.close();

		return id > 0;
	}

	public long getDocumentsCountFromForm(String server, String formWebId) {

		String whereClause = DocumentsTable.COL_SERVER + " = ? AND "
		        + DocumentsTable.COL_FORM_WEB_ID + " = ?";

		String[] whereArgs = new String[] { server, formWebId };

		Cursor cursor = database.query(DocumentsTable.NAME, new String[] { DocumentsTable.COL_ID },
		        whereClause, whereArgs, null, null, null);

		long formCount = cursor.getCount();

		cursor.close();
		return formCount;
	}

	public long getDocumentsCount(String server) {
		String whereClause = DocumentsTable.COL_SERVER + " = ?";

		String[] whereArgs = new String[] { server };

		Cursor cursor = database.query(DocumentsTable.NAME, new String[] { DocumentsTable.COL_ID },
		        whereClause, whereArgs, null, null, null);

		long formCount = cursor.getCount();

		cursor.close();
		return formCount;
	}

	public List<Document> getDocumentsWithOutgoingComment(String server) {

		List<Document> documents = new ArrayList<>();

		//	@formatter:off
		String query 	= "SELECT d.* FROM Documents d " 
						+ "LEFT JOIN Comments c "
            		        + "WHERE d.web_id = c.document_web_id "
            		        + "AND d.form_web_id = c.document_form_web_id "
            		        + "AND d.server = c.server "
            		        + "AND d.server = ? "
            		        + "AND c.is_outgoing = 1 ";
		//	@formatter:on

		FLLogger.d(TAG, query);
		Cursor cursor = database.rawQuery(query, new String[] { server });

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			try {
				Document document = cursorToDocument(cursor);
				documents.add(document);
			} catch (JSONException e) {
				e.printStackTrace();
			}
			cursor.moveToNext();
		}

		return documents;

	}

	// }}

	// ==============================================================
	// {{ Parser Methods

	private DocumentStub cursorToDocumentStub(Cursor cursor) {

		// @formatter:off
		int webIdIndex 			= cursor.getColumnIndexOrThrow(DocumentsTable.COL_WEB_ID);
		int formWebIdIndex 		= cursor.getColumnIndexOrThrow(DocumentsTable.COL_FORM_WEB_ID);
		int dateUpdatedIndex	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DATE_UPDATED);
		
		String webId 		= cursor.getString(webIdIndex);
		String formWebId 	= cursor.getString(formWebIdIndex);
		String dateUpdated 	= cursor.getString(dateUpdatedIndex);
		// @formatter:on

		DocumentStub documentStub = new DocumentStub();

		documentStub.webId = webId;
		documentStub.formWebId = formWebId;
		documentStub.dateUpdated = dateUpdated;

		return documentStub;

	}

	private Document cursorToDocument(Cursor cursor) throws JSONException {

		// @formatter:off
		// initialize indices

		int idIndex 		= cursor.getColumnIndexOrThrow(DocumentsTable.COL_ID);
		int webIdIndex 		= cursor.getColumnIndexOrThrow(DocumentsTable.COL_WEB_ID);
		int serverIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_SERVER);
		int formWebIdIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_FORM_WEB_ID);
		
		int trackingNumberIndex = cursor.getColumnIndexOrThrow(DocumentsTable.COL_TRACKING_NUMBER);
		int statusIndex			= cursor.getColumnIndexOrThrow(DocumentsTable.COL_STATUS);
		int processorWebIdIndex = cursor.getColumnIndexOrThrow(DocumentsTable.COL_PROCESSOR);
		int requestorWebIdIndex = cursor.getColumnIndexOrThrow(DocumentsTable.COL_REQUESTOR);
				
		int dateCreatedIndex 				= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DATE_CREATED);
		int dateUpdatedIndex				= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DATE_UPDATED);
		int dateCommentsLastUpdatedIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DATE_COMMENTS_LAST_UPDATED);
		
		int workflowIdIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_WORKFLOW_ID);
		int currentNodeIdIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_NODE_ID);
		
		int isStarredIndex 		= cursor.getColumnIndexOrThrow(DocumentsTable.COL_IS_STARRED);
		
		int fieldsIndex 		= cursor.getColumnIndexOrThrow(DocumentsTable.COL_FIELDS_JSON);
		
		int fieldsEnabledIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_FIELDS_ENABLED_JSON);
		int fieldsRequiredIndex = cursor.getColumnIndexOrThrow(DocumentsTable.COL_FIELDS_REQUIRED_JSON);
		int fieldsHiddenIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_FIELDS_HIDDEN_JSON);
		
		int availableActionsIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_ACTIONS_JSON);
		
		int documentRAWJSONIndex 	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DOCUMENT_RAW_JSON);
		int documentOwnerIdIndex	= cursor.getColumnIndexOrThrow(DocumentsTable.COL_DOCUMENT_OWNER_ID);
		
		// @formatter:on

		// initialize values

		String dbId = cursor.getString(idIndex);
		String webId = cursor.getString(webIdIndex);
		String server = cursor.getString(serverIndex);
		String formWebId = cursor.getString(formWebIdIndex);

		String trackingNumber = cursor.getString(trackingNumberIndex);
		String status = cursor.getString(statusIndex);
		String processorWebId = cursor.getString(processorWebIdIndex);
		String requestorWebId = cursor.getString(requestorWebIdIndex);

		String dateCreated = cursor.getString(dateCreatedIndex);
		String dateUpdated = cursor.getString(dateUpdatedIndex);
		String dateCommentsLastUpdated = cursor.getString(dateCommentsLastUpdatedIndex);

		String workflowId = cursor.getString(workflowIdIndex);
		String currentNodeId = cursor.getString(currentNodeIdIndex);

		String isStarred = cursor.getString(isStarredIndex);

		String fields = cursor.getString(fieldsIndex);

		String fieldsEnabledRaw = cursor.getString(fieldsEnabledIndex);
		String fieldsRequiredRaw = cursor.getString(fieldsRequiredIndex);
		String fieldsHiddenRaw = cursor.getString(fieldsHiddenIndex);

		String availableActionsRaw = cursor.getString(availableActionsIndex);

		String documentRAWJSON = cursor.getString(documentRAWJSONIndex);
		String documentOwnerId = cursor.getString(documentOwnerIdIndex);

		Document document = new Document();

		document.setDbId(dbId);
		document.setWebId(webId);
		document.setServer(server);
		document.setFormWebId(formWebId);

		document.setTrackingNumber(trackingNumber);
		document.setStatus(status);
		document.setProcessorWebId(processorWebId);
		document.setRequestorWebId(requestorWebId);

		document.setDateCreated(dateCreated);
		document.setDateUpdated(dateUpdated);
		document.setDateCommentsLastUpdated(dateCommentsLastUpdated);

		document.setWorkflowId(workflowId);
		document.setCurrentNodeId(currentNodeId);

		document.setStarred("1".equals(isStarred));

		// throws JSONException
		document.setFields(new JSONObject(fields));

		document.setRawFieldsEnabled(fieldsEnabledRaw);
		document.setRawFieldsRequired(fieldsRequiredRaw);
		document.setRawFieldsHidden(fieldsHiddenRaw);

		document.setRawActions(availableActionsRaw);
		document.setOwnerId(documentOwnerId);

		// throws JSONException
		document.setDocumentRawJSON(new JSONObject(documentRAWJSON));

		document = document.mapFieldProperties().mapActions();

		return document;

	}

	private ContentValues createCVFromDocument(Document document) {

		ContentValues cv = new ContentValues();

		// the db id is intentionally not set
		cv.put(DocumentsTable.COL_WEB_ID, document.getWebId());
		cv.put(DocumentsTable.COL_SERVER, document.getServer());
		cv.put(DocumentsTable.COL_FORM_WEB_ID, document.getFormWebId());

		cv.put(DocumentsTable.COL_TRACKING_NUMBER, document.getTrackingNumber());
		cv.put(DocumentsTable.COL_STATUS, document.getStatus());
		cv.put(DocumentsTable.COL_PROCESSOR, document.getProcessorWebId());
		cv.put(DocumentsTable.COL_REQUESTOR, document.getRequestorWebId());

		cv.put(DocumentsTable.COL_DATE_CREATED, document.getDateCreated());
		cv.put(DocumentsTable.COL_DATE_UPDATED, document.getDateUpdated());
		cv.put(DocumentsTable.COL_DATE_COMMENTS_LAST_UPDATED, document.getDateCommentsLastUpdated());

		cv.put(DocumentsTable.COL_WORKFLOW_ID, document.getWorkflowId());
		cv.put(DocumentsTable.COL_NODE_ID, document.getCurrentNodeId());

		cv.put(DocumentsTable.COL_IS_STARRED, (document.isStarred() ? "1" : "0"));

		cv.put(DocumentsTable.COL_FIELDS_ENABLED_JSON, document.getRawFieldsEnabled());
		cv.put(DocumentsTable.COL_FIELDS_REQUIRED_JSON, document.getRawFieldsRequired());
		cv.put(DocumentsTable.COL_FIELDS_HIDDEN_JSON, document.getRawFieldsHidden());

		cv.put(DocumentsTable.COL_FIELDS_JSON, document.getFields().toString());
		cv.put(DocumentsTable.COL_ACTIONS_JSON, document.getRawActions());
		cv.put(DocumentsTable.COL_DOCUMENT_OWNER_ID, document.getOwnerId());

		if ("0".equals(document.getWebId())) {
			cv.put(DocumentsTable.COL_DOCUMENT_RAW_JSON, "{}");
		} else {
			cv.put(DocumentsTable.COL_DOCUMENT_RAW_JSON, document.getDocumentRawJSON().toString());
		}

		return cv;

	}

	// }}

}
